package db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import db.Controller;
import db.dto.ManagerInDictDTO;

public class ManagerInDictDAO {
	public ManagerInDictDTO getManagerInDict(int id) {
		ManagerInDictDTO managerInDict = null;
		String query = "select * from m_managerindict where m_id = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				managerInDict = new ManagerInDictDTO();
				managerInDict.setId(rs.getInt("m_id"));
				managerInDict.setUserID(rs.getInt("m_userID"));
				managerInDict.setDictID(rs.getInt("m_dictID"));
			}
		} catch (SQLException e) {
			return null;
		}
		return managerInDict;
	}
	public List<ManagerInDictDTO> getManagerInDictUser(int userID) {
		int i = 0;
		List<ManagerInDictDTO> listManagerDict = new ArrayList<ManagerInDictDTO>();
		String query = "select * from m_managerindict where m_userID = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, userID);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				listManagerDict.add(i++, new ManagerInDictDTO(rs.getInt("m_id"), 
												rs.getInt("m_userID"), 
												rs.getInt("m_dictID")));
			}
		} catch (SQLException e) {
			return null;
		}
		return listManagerDict;
	}
	public List<ManagerInDictDTO> getManagerInDictDict(int dictID) {
		int i = 0;
		List<ManagerInDictDTO> listManagerDict = new ArrayList<ManagerInDictDTO>();
		String query = "select * from m_managerindict where m_dictID = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, dictID);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				listManagerDict.add(i++, new ManagerInDictDTO(rs.getInt("m_id"), 
														rs.getInt("m_userID"), 
														rs.getInt("m_dictID")));
			}
		} catch (SQLException e) {
			return null;
		}
		return listManagerDict;
	}
	public boolean deleteManagerInDict(int id) {
		String query = "delete from m_managerindict where m_id = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			stmt.executeUpdate();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
	public boolean deleteManagerInDictUser(int userID) {
		String query = "delete from m_managerindict where m_userID = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, userID);
			stmt.executeUpdate();
		} catch (SQLException e) {
			return false;
		}		
		return true;
	}
	public boolean deleteManagerInDictDict(int dictID) {
		String query = "delete from m_managerindict where m_dictID = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, dictID);
			stmt.executeUpdate();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
	public boolean deleteManagerInDict(int dictID, int userID) {
		String query = "delete from m_managerindict where m_dictID = ? and m_userID = ?";
		Connection con = Controller.getConnection();
		try {
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setInt(1, dictID);
			stmt.setInt(2, userID);
			stmt.executeUpdate();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
	public boolean addManagerInDict(int userID,int dictID) {
		String query = "insert into m_managerindict(m_userID, m_dictID) values (?, ?)";
		Connection con = Controller.getConnection();
		PreparedStatement stmt;
		try {
			stmt = con.prepareStatement(query);
			stmt.setInt(1, userID);
			stmt.setInt(2, dictID);
			stmt.executeUpdate();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
}
